﻿Imports System.Data
Imports System.Data.SqlClient
Imports Conexao_liion

Partial Class CustoDeImportacao
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        'Faz com que ao sair do aplicativo, limpe o buffer e não podendo entrar na página através do butão voltar do Browser.
        Response.Cache.SetCacheability(HttpCacheability.ServerAndNoCache)

        Try
            'If Not Session("Logged") Then
            ' Session("ShowSessionExpired") = 1 ' Show lblSessionExpired in Login.aspx
            ' Response.Redirect("Login.aspx")
            'End If

            If Not Page.IsPostBack Then


                Dim ConnectionTest As String
                ConnectionTest = "A"

                ' Header
                lblModulo.Text = "Operações"
                lblModuloItem.Text = "Custo de Importação"

                btnIncluir.Enabled = False
                btnConsultar.Enabled = False
                btnAlterar.Enabled = False
                btnExcluir.Enabled = False
                btnDadosImportacao.Enabled = False


                If (LoadHolding(ConnectionTest) = True) Then

                    FillDDL()

                    If (CarregaGVW() = True) Then
                        If (Session("sNM_GRUPOACESSO") = 3 Or Session("sNM_GRUPOACESSO") = 4 Or Session("sNM_GRUPOACESSO") = 5) Then
                            btnIncluir.Enabled = False
                            btnAlterar.Enabled = False
                            btnExcluir.Enabled = False
                        Else
                            btnIncluir.Enabled = True
                        End If

                    End If

                End If

            End If

        Catch ex As Exception
            lblMsg.Text = ex.Message
        End Try

    End Sub

    ' Step 02
    Private Function LoadHolding(ByVal strConn As String) As Boolean

        LoadHolding = False

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String
            theQueryString = "SELECT CodigoDaHolding, NomeDaHolding" & _
                         " FROM Holding;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                If (theSqlDataReader.HasRows = True) Then
                    ' Loop through all the rows in the DataTableReader.
                    Do While theSqlDataReader.Read()
                        lblHolding.Text = theSqlDataReader(1)
                        lblHolding.ToolTip = theSqlDataReader(0)
                    Loop

                    LoadHolding = True

                Else
                    lblMsg.Text = "Holding não cadastrada no Sistema."
                End If

            Catch ex As Exception
                lblMsg.Text = ex.Message
            End Try

        End Using

    End Function

    ' Step 03
    Private Sub FillDDL()

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT CodigoDaCia, CodigoDaCia + ' - ' + NomeDaCia AS Cia" & _
                     " FROM Companhia" & _
                     " WHERE CodigoDaHolding = '" & lblHolding.ToolTip & "'" & _
                     " ORDER BY CodigoDaCia;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)
                ddlCompanhia.DataTextField = "Cia"
                ddlCompanhia.DataValueField = "CodigoDaCia"
                ddlCompanhia.DataSource = mydatatable
                ddlCompanhia.DataBind()

                ddlCompanhia.Items.Insert(0, "")

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using


        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT CodFornecedorServico, RazaoSocialFornecedorServico AS FornecedorServico" & _
                         " FROM fornecedorservico" & _
                         " ORDER BY RazaoSocialFornecedorServico;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)
                ddlFornecedor.DataTextField = "FornecedorServico"
                ddlFornecedor.DataValueField = "CodFornecedorServico"
                ddlFornecedor.DataSource = mydatatable
                ddlFornecedor.DataBind()

                ddlFornecedor.Items.Insert(0, "")

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Sub

    ' Step 04
    Private Function CarregaGVW() As Boolean

        Dim strWhere As String = ""
        CarregaGVW = False

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String = "SELECT A.CodigoDaHolding, A.CodigoDaCia, A.CodigoDoEstabelecimento, A.CodFornecedorServico, A.NumeroDocumento, A.DataDocumento, A.DataLancamento, A.DI, A.DataRegistro" & _
                     " FROM custoimportacao A"

            If Not (ddlCompanhia.Text = "") Then
                strWhere = " AND A.CodigoDaCia = '" & ddlCompanhia.SelectedValue & "'"
            End If

            If Not (ddlEstabelecimento.Text = "") Then
                strWhere = strWhere & _
                           " AND A.CodigoDoEstabelecimento = '" & ddlEstabelecimento.SelectedValue & "'"
            End If

            If Not (Trim(txtOrdemCompra.Text) = "") Then
                txtOrdemCompra.Text = Trim(txtOrdemCompra.Text)

                If (strWhere = "") Then
                    strWhere = " AND A.NumeroDocumento LIKE '%" & txtOrdemCompra.Text & "%'"
                Else
                    strWhere = strWhere & _
                               " AND A.NumeroDocumento LIKE '%" & txtOrdemCompra.Text & "%'"
                End If
            End If

            If Not (ddlFornecedor.Text = "") Then
                If (strWhere = "") Then
                    strWhere = " AND A.CodFornecedorServico = '" & ddlFornecedor.SelectedValue & "'"
                Else
                    strWhere = strWhere & _
                               " AND A.CodFornecedorServico = '" & ddlFornecedor.SelectedValue & "'"
                End If
            End If

            If Not (strWhere = "") Then
                theQueryString = theQueryString & _
                         " WHERE A.CodigoDaHolding = '" & lblHolding.ToolTip & "'" & strWhere
            End If

            theQueryString = theQueryString & _
                     " ORDER BY A.CodigoDaCia, A.CodigoDoEstabelecimento, A.NumeroDocumento DESC;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)
                gvwData.DataSource = mydatatable
                gvwData.DataBind()

                CarregaGVW = True

                theSqlDataReader.Close()
                CarregaGVW = True
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Function


    ' Step 05
    Protected Sub ddlCompanhia_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlCompanhia.SelectedIndexChanged

        FillDDLEstabelecimento()

    End Sub

    ' Step 06
    Private Sub FillDDLEstabelecimento()

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT CodigoDoEstabelecimento" & _
                     " FROM Estabelecimento" & _
                     " WHERE CodigoDaHolding = '" & lblHolding.ToolTip & "'" & _
                     " AND CodigoDaCia = '" & ddlCompanhia.SelectedValue & "'" & _
                     " ORDER BY CodigoDoEstabelecimento;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)
                ddlEstabelecimento.DataTextField = "CodigoDoEstabelecimento"
                ddlEstabelecimento.DataValueField = "CodigoDoEstabelecimento"
                ddlEstabelecimento.DataSource = mydatatable
                ddlEstabelecimento.DataBind()

                ddlEstabelecimento.Items.Insert(0, "")

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Sub

    ' Step 07
    Protected Sub btnPesquisar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnPesquisar.Click

        CarregaGVW()

    End Sub

    ' Step 08
    Protected Sub gvwData_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles gvwData.PageIndexChanging

        Try
            CarregaGVW()
            gvwData.PageIndex = e.NewPageIndex
            gvwData.DataBind()
        Catch ex As Exception
            lblMsg.Text = ex.Message
        End Try

    End Sub

    ' Step 09
    Protected Sub gvwData_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles gvwData.SelectedIndexChanged

        Dim strSituacao As String = ""

        Try

            If (Session("sNM_GRUPOACESSO") = 3 Or Session("sNM_GRUPOACESSO") = 4 Or Session("sNM_GRUPOACESSO") = 5) Then
                btnIncluir.Enabled = False
                btnAlterar.Enabled = True
                btnExcluir.Enabled = True
                btnDadosImportacao.Enabled = True
                btnCustoII.Enabled = True
                btnDespesasAduaneiras.Enabled = True
                btnConsultar.Enabled = True
                btnFatura.Enabled = True
            Else

                btnConsultar.Enabled = True
                btnAlterar.Enabled = True
                btnExcluir.Enabled = True
                btnDadosImportacao.Enabled = True
                btnCustoII.Enabled = True
                btnDespesasAduaneiras.Enabled = True
                btnFatura.Enabled = True
            End If

        Catch ex As Exception
            lblMsg.Text = ex.Message
        End Try

    End Sub

    Private Function ExistOCDFaturada(ByVal strConn As String) As Boolean

        Dim intSituacaoOrdemCompra As Integer = 0

        ExistOCDFaturada = False

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String
            theQueryString = "SELECT CodSituacaoOrdemCompra" & _
                         " FROM OrdemCompraDetail" & _
                         " WHERE CodigoDaHolding = '" & lblHolding.ToolTip & "'" & _
                         " AND CodigoDaCia = '" & HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(0).Text) & "'" & _
                         " AND CodigoDoEstabelecimento = '" & HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(1).Text) & "'" & _
                         " AND OrdemCompra = '" & HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(2).Text) & "'" & _
                         " AND SeqOrdemCompra = " & HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(3).Text) & ";"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                If (theSqlDataReader.HasRows = True) Then
                    ' Loop through all the rows in the DataTableReader.
                    Do While theSqlDataReader.Read()
                        If (theSqlDataReader!CodSituacaoOrdemCompra = 4) Then ' Faturada
                            ExistOCDFaturada = True
                            Exit Do
                        End If
                    Loop
                End If

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Function

    ' Step 10
    Protected Sub btnIncluir_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnIncluir.Click

        Session.Add("strOperacao", "I") ' Incluir

        Session.Add("strCodigoDaHolding", "")
        Session.Add("strCodigoDaCia", "")
        Session.Add("strCodigoDoEstabelecimento", "")
        Session.Add("strCodFornecedorServico", "")
        Session.Add("strNumeroDocumento", "")
        Session.Add("strDataDocumento", "")

        Response.Redirect("CustoDeImportacaoData.aspx")

    End Sub

    ' Step 11
    Protected Sub BtnConsultar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnConsultar.Click

        Session.Add("strOperacao", "C") ' Consultar

        Session.Add("strCodigoDaHolding", lblHolding.ToolTip)
        Session.Add("strCodigoDaCia", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(0).Text))
        Session.Add("strCodigoDoEstabelecimento", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(1).Text))
        Session.Add("strCodFornecedorServico", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(2).Text))
        Session.Add("strNumeroDocumento", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(3).Text))
        Session.Add("strDataDocumento", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(4).Text))
        Session.Add("strDI", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(5).Text))

        Response.Redirect("CustoDeImportacaoData.aspx")

    End Sub

    ' Step 12
    Protected Sub btnAlterar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAlterar.Click

        Session.Add("strOperacao", "A") ' Alterar

        Session.Add("strCodigoDaHolding", lblHolding.ToolTip)
        Session.Add("strCodigoDaCia", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(0).Text))
        Session.Add("strCodigoDoEstabelecimento", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(1).Text))
        Session.Add("strCodFornecedorServico", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(2).Text))
        Session.Add("strNumeroDocumento", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(3).Text))
        Session.Add("strDataDocumento", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(4).Text))
        Session.Add("strDI", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(5).Text))

        Response.Redirect("CustoDeImportacaoData.aspx")

    End Sub

    ' Step 13
    Protected Sub btnExcluir_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExcluir.Click

        Session.Add("strOperacao", "E") ' Excluir

        Session.Add("strCodigoDaHolding", lblHolding.ToolTip)
        Session.Add("strCodigoDaCia", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(0).Text))
        Session.Add("strCodigoDoEstabelecimento", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(1).Text))
        Session.Add("strCodFornecedorServico", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(2).Text))
        Session.Add("strNumeroDocumento", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(3).Text))
        Session.Add("strDataDocumento", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(4).Text))
        Session.Add("strDI", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(5).Text))

        Response.Redirect("CustoDeImportacaoData.aspx")

    End Sub

    ' Step 14
    Protected Sub btnDadosImportacao_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnDadosImportacao.Click

        Session.Add("strOperacao", "A") ' Vazio

        Session.Add("strHolding", lblHolding.Text)
        Session.Add("strCodigoDaHolding", lblHolding.ToolTip)
        Session.Add("strCodigoDaCia", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(0).Text))
        Session.Add("strCodigoDoEstabelecimento", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(1).Text))
        Session.Add("strCodFornecedorServico", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(2).Text))
        Session.Add("strNumeroDocumento", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(3).Text))
        Session.Add("strDataDocumento", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(4).Text))
        Session.Add("strDI", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(5).Text))

        Response.Redirect("CustoDeImportacaoDados.aspx")

    End Sub

    ' Step 15
    Protected Sub btnVoltar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnVoltar.Click

        Response.Redirect("Default.aspx")

    End Sub

    Protected Sub btnCustoII_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnCustoII.Click

        Session.Add("strOperacao", "A") ' Vazio

        Session.Add("strHolding", lblHolding.Text)
        Session.Add("strCodigoDaHolding", lblHolding.ToolTip)
        Session.Add("strCodigoDaCia", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(0).Text))
        Session.Add("strCodigoDoEstabelecimento", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(1).Text))
        Session.Add("strCodFornecedorServico", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(2).Text))
        Session.Add("strNumeroDocumento", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(3).Text))
        Session.Add("strDataDocumento", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(4).Text))
        Session.Add("strDI", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(5).Text))

        Response.Redirect("CustoDeImportacaoImpostos.aspx")

    End Sub

    Protected Sub btnDespesasAduaneiras_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnDespesasAduaneiras.Click

        Session.Add("strOperacao", "A") ' Vazio

        Session.Add("strHolding", lblHolding.Text)
        Session.Add("strCodigoDaHolding", lblHolding.ToolTip)
        Session.Add("strCodigoDaCia", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(0).Text))
        Session.Add("strCodigoDoEstabelecimento", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(1).Text))
        Session.Add("strCodFornecedorServico", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(2).Text))
        Session.Add("strNumeroDocumento", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(3).Text))
        Session.Add("strDataDocumento", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(4).Text))
        Session.Add("strDI", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(5).Text))

        Response.Redirect("CustoDeImportacaoDespAduaneiras.aspx")

    End Sub

    Protected Sub btnFatura_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnFatura.Click

        Session.Add("strOperacao", "A") ' Vazio

        Session.Add("strHolding", lblHolding.Text)
        Session.Add("strCodigoDaHolding", lblHolding.ToolTip)
        Session.Add("strCodigoDaCia", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(0).Text))
        Session.Add("strCodigoDoEstabelecimento", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(1).Text))
        Session.Add("strCodFornecedorServico", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(2).Text))
        Session.Add("strNumeroDocumento", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(3).Text))
        Session.Add("strDataDocumento", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(4).Text))
        'Session.Add("strDI", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(5).Text))

        Response.Redirect("CustoFatura.aspx")

    End Sub

End Class
